#nullable enable

using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using PHMEE.CommonUtils;
using PHMEE.TDengineInfrastructure;

namespace PHMEE.EnergyEfficiency;

public class AnalogTDRepository : BaseTdRepository, IAnalogTDRepository
{
    private readonly ILogger<AnalogTDRepository> _logger;

    /// <summary>
    /// Analog 位于 Ams 库
    /// </summary>
    private const DbPrefixEnum DbPrefix = DbPrefixEnum.Ams;

    public AnalogTDRepository(IOptionsSnapshot<TDengineOptions> tdengineOptions, ILogger<AnalogTDRepository> logger) :
        base(tdengineOptions)
    {
        _logger = logger;
    }

    // 查询 analog 超级表下的每一张子表中的指定时间范围内的最新一条数据，前闭后闭区间
    public async Task<List<Analog>> GetLatestByDateTimeCloseCloseAsync(Guid vesselId, DateTime startDateTime,
        DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Analog> analogs = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, LAST(v) as v, u, tagname, tagdesc, ams_group, group_index FROM {dbName}.analog WHERE ts >= '{startDateTime.ToFormattedUtcString()}' AND ts <= '{endDateTime.ToFormattedUtcString()}' GROUP BY tbname;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                analogs.Add(new Analog
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = reader.GetValue(1) as float? ?? default,
                    U = (AmsXdrUnitType)reader.GetInt32(2),
                    TagName = reader.GetString(3),
                    TagDesc = reader.GetString(4),
                    AmsGroup = reader.GetString(5),
                    GroupIndex = reader.GetInt32(6)
                });
            }

            return analogs;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(AnalogTDRepository)}.{nameof(GetLatestByDateTimeCloseCloseAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据时间范围查询 Analog 数据失败", ex);
        }
    }


    // 根据 tagname 查询 analog 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    public async Task<List<Analog>> GetAnalogSignalsByTagnameAsync(Guid vesselId, string tagName,
        DateTime startDateTime, DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Analog> analogSignals = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, v, u, tagname, tagdesc FROM {dbName}.analog WHERE ts >= '{startDateTime.ToFormattedUtcString()}' AND ts < '{endDateTime.ToFormattedUtcString()}' AND tagname LIKE '%{tagName}%' ORDER BY ts DESC;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                analogSignals.Add(new Analog
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = reader.GetValue(1) as float? ?? default,
                    U = (AmsXdrUnitType)reader.GetInt32(2),
                    TagName = reader.GetString(3),
                    TagDesc = reader.GetString(4)

                    // 不查询分组信息
                });
            }

            return analogSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(AnalogTDRepository)}.{nameof(GetAnalogSignalsByTagnameAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据 tagname 查询 Analog 数据失败");
        }
    }

    // 根据 tagname 列表查询这些 tagName 对应 endDateTime 前（含）的数据，每个 tag 仅返回最新的一条数据，不包含分组信息
    public async Task<List<Analog>> GetAnalogSignalsByTagnamesAsync(Guid vesselId, List<string> tagNames,
        DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Analog> analogSignals = [];

            await using var command = TDConnection.CreateCommand();

            // command.CommandText = 
            //     $"SELECT ts, LAST(v) as v, u, tagname, tagdesc FROM {dbName}.analog WHERE ts <= '{endDateTime.ToFormatString()}' AND tagname IN ('{string.Join("','", tagNames)}') GROUP BY tbname;";

            command.CommandText =
                $"SELECT ts, LAST(v) as v, u, tagname, tagdesc FROM {dbName}.analog WHERE ts <= '{endDateTime.ToFormattedUtcString()}'" +
                (tagNames.Count > 0 ? $" AND tagname IN ('{string.Join("','", tagNames)}')" : "") + " GROUP BY tbname;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                analogSignals.Add(new Analog
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = reader.GetValue(1) as float? ?? default,
                    U = (AmsXdrUnitType)reader.GetInt32(2),
                    TagName = reader.GetString(3),
                    TagDesc = reader.GetString(4)

                    // 不查询分组信息
                });
            }

            return analogSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(AnalogTDRepository)}.{nameof(GetAnalogSignalsByTagnamesAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, null, endDateTime, "根据 tagname 列表查询 Analog 数据失败");
        }
    }

    // 获取所有 tagdesc
    public async Task<List<string>> GetAllAnalogTagdescAsync(Guid vesselId)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<string> allTagdesc = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT DISTINCT tagdesc FROM {dbName}.analog;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                allTagdesc.Add(reader.GetString(0));
            }

            return allTagdesc;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(AnalogTDRepository)}.{nameof(GetAllAnalogTagdescAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, null, null, "获取所有 tagdesc 失败");
        }
    }

    // 根据 tagdescription 查询 analog 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    public async Task<List<Analog>> GetAnalogSignalsByTagdescAsync(Guid vesselId, IEnumerable<string> tagDescs,
        DateTime startDateTime, DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Analog> analogSignals = [];

            await using var command = TDConnection.CreateCommand();

            // 将 ' 转义为 ''
            command.CommandText =
                $"SELECT ts, v, u, tagname, tagdesc FROM {dbName}.analog WHERE ts >= '{startDateTime.ToFormattedUtcString()}' AND ts < '{endDateTime.ToFormattedUtcString()}' AND tagdesc IN ('{string.Join("','", tagDescs.Select(mt => mt.Replace("'", "''")))}') ORDER BY ts DESC;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                analogSignals.Add(new Analog
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = reader.GetValue(1) as float? ?? default,
                    U = (AmsXdrUnitType)reader.GetInt32(2),
                    TagName = reader.GetString(3),
                    TagDesc = reader.GetString(4)

                    // 不查询分组信息
                });
            }

            return analogSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(AnalogTDRepository)}.{nameof(GetAnalogSignalsByTagdescAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据 tagdesc 查询 Analog 数据失败");
        }
    }
    
    // 根据条件，查询 analog 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    public async Task<(int totalCount, List<Analog> analogs)> GetAnalogSignalsAsync(Guid vesselId, string? tagName, List<string>? tagDescs,
        DateTime startDateTime, DateTime endDateTime, int skipCount, int maxResultCount)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Analog> analogSignals = [];
            
            List<string> whereConditions =
            [
                $"ts >= '{startDateTime.ToFormattedUtcString()}'",
                $"ts < '{endDateTime.ToFormattedUtcString()}'"
            ];

            if (!string.IsNullOrWhiteSpace(tagName))
            {
                whereConditions.Add($"tagname LIKE '%{tagName}%'");
            }
            if (tagDescs != null && tagDescs.Count != 0)
            {
                whereConditions.Add($"tagdesc IN ('{string.Join("','", tagDescs.Select(mt => mt.Replace("'", "''")))}')");
            }

            string whereClause = whereConditions.Count > 0 ? " WHERE " + string.Join(" AND ", whereConditions) : string.Empty;

            await using var command = TDConnection.CreateCommand();
            
            // Query total count
            command.CommandText = $"SELECT COUNT(*) FROM {dbName}.analog {whereClause};";
            var totalCount = Convert.ToInt32(await command.ExecuteScalarAsync());

            // // Query actual data
            command.CommandText =
                $"SELECT ts, v, u, tagname, tagdesc FROM {dbName}.analog {whereClause} ORDER BY ts DESC LIMIT {maxResultCount} OFFSET {skipCount};";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                analogSignals.Add(new Analog
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = reader.GetValue(1) as float? ?? default,
                    U = (AmsXdrUnitType)reader.GetInt32(2),
                    TagName = reader.GetString(3),
                    TagDesc = reader.GetString(4)

                    // 不查询分组信息
                });
            }

            return (totalCount, analogSignals);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(AnalogTDRepository)}.{nameof(GetAnalogSignalsAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据条件查询 Analog 数据失败");
        }
    }
}